--    Author    : MaYasha
--    Name      : EDW.DS_LOAF_SUM.HQL
--    Functions : 
--    Purpose   : Monthly saving the variation data from detail 
--    DataSource: EDW.EV_LOAF_TRAN
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-05-03  ChenErHao           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE EDW.DS_LOAF_SUM PARTITION (DATA_DATE = '#V_DATA_DATE#' ,DATA_SRC_ORG = '#V_DATA_SRC_ORG#')
SELECT 
    FIN_ORG_NO                               -- 金融机构编码
    ,CLIENT_TYPE                             -- 客户类型
    ,CLIENT_NO                               -- 借款人代码
    ,CLIENT_INDUSTRY                         -- 贷款主体行业类别
    ,CLIENT_DIST                             -- 借款人所在地区
    ,ECONOMY_TYPE                            -- 企业出资人经济成分
    ,ENTERPRISE_SCALE                        -- 企业规模
    ,BUSINESS_TYPE                           -- 贷款产品类别
    ,LOAN_INDUSTRY                           -- 贷款实际投向
    ,DRAWDOWN_DATE                           -- 贷款发放日期
    ,MATURITY_DATE                           -- 贷款到期日期
    ,ACTUAL_END_DATE                         -- 贷款实际终止日期
    ,CCY                                     -- 贷款币种
    ,SUM(CASE WHEN LOAF.DATA_DATE = '#V_DATA_DATE#' THEN NVL(OCCUR_AMOUNT,0) ELSE 0 END) AS OCCUR_AMOUNT -- 贷款发生金额
    ,IF_FLOAT                                -- 利率是否固定
    ,RATE                                    -- 利率水平
    ,GUARANTY_TYPE                           -- 贷款担保方式
    ,LOAN_STATUS                             -- 贷款状态
    ,DRAWDOWN_OR_REPAY                       -- 贷款发放收回标志
    ,TERM_CODE                               -- 贷款期限
    ,SUM(CASE WHEN LOAF.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_AMOUNT,0) ELSE 0 END) AS WSUM_AMOUNT -- 发生额加权值
    ,0 -- SUM(CASE WHEN LOAF.DATA_DATE = '#V_DATA_DATE#' THEN NVL(OCCUR_AMOUNT,0) ELSE 0 END) AS AMOUNT_LM  -- 上期发生额
    ,SUM(CASE WHEN (LOAF.DATA_DATE <= '#V_DATA_DATE#' AND LOAF.DATA_DATE LIKE CONCAT(SUBSTRING('#V_DATA_DATE#',1,4),'%')) THEN NVL(OCCUR_AMOUNT,0) ELSE 0 END) AS AMOUNT_YS  -- 当年累计发生额
    ,0 -- SUM(CASE WHEN LOAF.DATA_DATE = '#V_DATA_DATE#' THEN NVL(OCCUR_AMOUNT,0) ELSE 0 END) AS AMOUNT_YC  -- 去年同期发生额
    ,0 -- SUM(CASE WHEN LOAF.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_AMOUNT,0) ELSE 0 END) AS WAMOUNT_LM -- 发生额加权上期值
    ,SUM(CASE WHEN (LOAF.DATA_DATE <= '#V_DATA_DATE#' AND LOAF.DATA_DATE LIKE CONCAT(SUBSTRING('#V_DATA_DATE#',1,4),'%')) THEN NVL(WSUM_AMOUNT,0) ELSE 0 END) AS WAMOUNT_YS -- 发生额加权值当年累计
    ,0 -- SUM(CASE WHEN LOAF.DATA_DATE = '#V_DATA_DATE#' THEN NVL(WSUM_AMOUNT,0) ELSE 0 END) AS WAMOUNT_YC -- 发生额加权值去年同期
    ,ORG_TYPE                                -- 机构类型
    ,TERM_TYPE                               -- 期限类型
FROM EDW.EV_LOAF_TRAN LOAF
WHERE ((LOAF.DATA_DATE <= '#V_DATA_DATE#' AND LOAF.DATA_DATE LIKE CONCAT(SUBSTRING('#V_DATA_DATE#',1,4),'%')) -- 本年截止当期
       OR LOAF.DATA_DATE <= '#V_DATA_DATE#' -- 上期
       OR LOAF.DATA_DATE <= '#V_DATA_DATE#' -- 去年同期
       )
AND LOAF.DATA_SRC_ORG = '#V_DATA_SRC_ORG#'
GROUP BY FIN_ORG_NO       
         ,CLIENT_TYPE     
         ,CLIENT_NO       
         ,CLIENT_INDUSTRY 
         ,CLIENT_DIST     
         ,ECONOMY_TYPE    
         ,ENTERPRISE_SCALE
         ,BUSINESS_TYPE   
         ,LOAN_INDUSTRY   
         ,DRAWDOWN_DATE   
         ,MATURITY_DATE   
         ,ACTUAL_END_DATE 
         ,CCY
         ,IF_FLOAT                  
         ,RATE             
         ,GUARANTY_TYPE    
         ,LOAN_STATUS      
         ,DRAWDOWN_OR_REPAY
         ,TERM_CODE
         ,ORG_TYPE          
         ,TERM_TYPE